﻿Imports System.Data
Imports System.Data.SqlClient
Public Class UsuarioDAL
    Public ObjConexao As New Conexao

#Region "Incluir/Alterar"
    Public Function InserirAlterar(Codigo As Integer, login As String, senha As String, status As Char, usuario As String) As Integer

        Dim cmd = New SqlCommand
        Try
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "SPUsuario"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = Codigo
            cmd.Parameters.Add(New SqlParameter("@login", SqlDbType.VarChar)).Value = login
            cmd.Parameters.Add(New SqlParameter("@senha", SqlDbType.VarChar)).Value = senha
            cmd.Parameters.Add(New SqlParameter("@status", SqlDbType.VarChar)).Value = status
            If (Not IsNothing(usuario)) Then
                cmd.Parameters.Add(New SqlParameter("@usuario", SqlDbType.VarChar)).Value = usuario
            Else
                cmd.Parameters.Add(New SqlParameter("@usuario", SqlDbType.VarChar)).Value = ""
            End If

            Codigo = ObjConexao.executaCmd(cmd)
            Return Codigo
        Catch ex As Exception
            Throw ex
        End Try
    End Function
#End Region

#Region "Valida exclusão"
    Public Function ValidaExcluir(ByVal Codigo As String) As Boolean
        Dim cmd = New SqlCommand
        Dim tab As DataTable
        Dim ret As Boolean = True
        Try
            cmd.CommandText = "select*from Mul_Usuarios u inner join Mul_Ocorrencia o on u.usu_login=o.oco_usuario where u.usu_login=@login"
            cmd.Parameters.Add(New SqlParameter("@login", SqlDbType.VarChar)).Value = Codigo
            tab = ObjConexao.executaConsulta(cmd)
            If (tab.Rows.Count > 0) Then
                ret = False
            End If
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return ret
    End Function
#End Region

#Region "Excluir"
    Public Function Excluir(Codigo As Integer) As Integer
        Dim cmd = New SqlCommand
        Try
            cmd.CommandText = "Delete from Mul_usuarios where usu_codigo=@codigo"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = Codigo
            Codigo = ObjConexao.executaCmd(cmd)
            Return Codigo
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
    End Function
#End Region

#Region "Buscar"
    Public Function Buscar(ByVal Descricao As String, tipo As Integer) As DataTable
        Dim tab = New DataTable
        Dim cmd As SqlCommand
        Try
            cmd = New SqlCommand
            Select Case tipo
                Case 0
                    cmd.CommandText = "select * from Mul_usuarios where usu_codigo = @codigo"
                    cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = Val(Descricao)
                Case 1
                    cmd.CommandText = "select * from Mul_usuarios where usu_login COLLATE SQL_Latin1_General_CP1_CI_AI like @descricao"
                    cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar)).Value = "%" & Descricao & "%"
                Case Else
                    cmd.CommandText = "select * from Mul_usuarios order by usu_login"
            End Select

            tab = ObjConexao.executaConsulta(cmd)
            tab.DefaultView.AllowNew = False
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return tab
    End Function
#End Region

#Region "ValidaReplica"
    Function ValidaReplica(Codigo As Integer, Descricao As String) As Boolean
        Dim tab = New DataTable
        Dim cmd As SqlCommand
        Try
            cmd = New SqlCommand
            cmd.CommandText = "select * from Mul_usuarios where usu_login=@descricao and usu_codigo<>@codigo "
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = Codigo
            cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar)).Value = Descricao
            tab = ObjConexao.executaConsulta(cmd)
            tab.DefaultView.AllowNew = False
            If (tab.Rows.Count = 0) Then
                Return True
            Else
                Return False
            End If
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
    End Function
#End Region

#Region "Login"
    Function login(senha As String, Descricao As String) As String
        Dim tab = New DataTable
        Dim cmd As SqlCommand
        Try
            cmd = New SqlCommand
            cmd.CommandText = "select * from Mul_usuarios where usu_login=@descricao "
            cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar)).Value = Descricao
            tab = ObjConexao.executaConsulta(cmd)
            tab.DefaultView.AllowNew = False
            If (tab.Rows.Count > 0) Then
                If (senha = tab.Rows(0)("usu_senha").ToString) Then
                    If ("A" = tab.Rows(0)("usu_status").ToString) Then
                        Return tab.Rows(0)("usu_login").ToString
                    Else
                        Return "Usuário Inativo"
                    End If

                Else
                    Return "Senha Inválida"
                End If

            Else
                Return "Login Inválido"
            End If
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
    End Function
#End Region
End Class
